package com.gwclh.personnel.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.gwclh.personnel.entity.Internship;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import org.apache.ibatis.annotations.Select;

import java.util.Map;

/**
 * <p>
 * 实习表，说明，若要查询实习员工，通过查询状态为0与2的emp_id，若查询已转正员工，则查询状态为1 Mapper 接口
 * </p>
 *
 * @author gzhhh
 * @since 2020-07-24
 */
public interface InternshipMapper extends BaseMapper<Internship> {

    @Select("select t1.id,t1.emp_name,t2.start_date,t2.end_date,t2.intern_status,t3.dept_name,t4.sta_name\n" +
            "from t_employee t1\n" +
            "left join t_internship t2\n" +
            "on t1.id=t2.emp_id\n" +
            "left join t_department t3\n" +
            "on t1.dept_id=t3.id\n" +
            "left join t_station t4\n" +
            "on t1.sta_id=t4.id")
    IPage<Map<String,Object>> getInternship(IPage<Map<String,Object>> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select t1.id,t1.emp_name,t2.start_date,t2.end_date,t2.intern_status,t3.dept_name,t4.sta_name\n" +
            "from t_employee t1\n" +
            "left join t_internship t2\n" +
            "on t1.id=t2.emp_id\n" +
            "left join t_department t3\n" +
            "on t1.dept_id=t3.id\n" +
            "left join t_station t4\n" +
            "on t1.sta_id=t4.id where t1.id=#{empId} or t1.emp_name=#{empName} or t3.dept_name=#{deptName}\n" +
            "or t4.sta_name=#{staName} or t2.intern_status=#{internStatus}\n" +
            "or (t2.start_date>=#{startDate} and t2.end_date<=#{endDate})")
    IPage<Map<String,Object>> getInternshipBySearch(IPage<Map<String,Object>> page, @Param(Constants.WRAPPER) Wrapper wrapper
            , Integer empId, String empName
            , String deptName, String staName, Integer internStatus
            , Date startDate, Date endDate);

    @Select("select t1.id,t1.emp_name,t2.start_date,t2.end_date,t2.intern_status,t3.dept_name,t4.sta_name\n" +
            "from t_employee t1\n" +
            "left join t_internship t2\n" +
            "on t1.id=t2.emp_id\n" +
            "left join t_department t3\n" +
            "on t1.dept_id=t3.id\n" +
            "left join t_station t4\n" +
            "on t1.sta_id=t4.id where t2.intern_status=1")
    IPage<Map<String,Object>> selectFormal(IPage<Map<String,Object>> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select t1.id,t1.emp_name,t2.start_date,t2.end_date,t2.intern_status,t3.dept_name,t4.sta_name\n" +
            "from t_employee t1\n" +
            "left join t_internship t2\n" +
            "on t1.id=t2.emp_id\n" +
            "left join t_department t3\n" +
            "on t1.dept_id=t3.id\n" +
            "left join t_station t4\n" +
            "on t1.sta_id=t4.id where t2.intern_status=1 and (t1.id=#{empId}\n" +
            "or t1.emp_name=#{empName} or t3.dept_name=#{deptName}\n" +
            "or (t2.start_date>=#{startDate} and t2.end_date<=#{endDate}))")
    IPage<Map<String,Object>> selectFormalBySearch(IPage<Map<String,Object>> page, @Param(Constants.WRAPPER) Wrapper wrapper
            , Integer empId, String empName
            , String deptName, Date startDate, Date endDate);
    /**
     * 查询当前月的入职员工数
     * @return
     */
    @Select("SELECT DATE_FORMAT(oper_date,'%Y-%m') as time,count(*) AS total FROM t_internship WHERE intern_status = 1 AND DATE_FORMAT(oper_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY time")
    Map<String,Object> statisticsInternshipMonth();
}
